﻿#include "account_db.h"

account_db::account_db(QString db_name)
{
    QString temp_path_name = ACCOUNT_DB_PATH+db_name;
     create_database("QSQLITE",temp_path_name);
     //创建登录用户信息存储数据的数据库
     account_data_db = get_instance_db(temp_path_name);
     create_table(account_data_db,"person",CREATE_PERSON);
     create_table(account_data_db,"m_group",CREATE_GROUP);
     create_table(account_data_db,"relation",CREATE_RELATION);
}

QStringList account_db::account_find_groups()
/*
 * 根据用户信息返回存在的账簿分组
 *
 */
{
    QStringList m_list;
    QSqlQuery query(account_data_db);
    query.exec(SELECT_GROUP);
     while(query.next()){
         QString t_name = query.value(0).toString();
         m_list.append(t_name);
     }
    return m_list;
}


/*
**************************************以下部分为 插入/删除 数据 的业务处理部分**************
*/
bool account_db::insert_check(QString table,QString check_row, QString check_data)
/*
 * 所有数据在插入之前都需要检查是否有重复项
 */
{
     QSqlQuery query(account_data_db);
     bool val = false;
     query.exec(QString("select %1 from %2").arg(check_row).arg(table));
     while(query.next()){
         QString t_data = query.value(0).toString();
         if(t_data == check_data){
             val = true;
             return val;
         }
     }
     return val;
}

char account_db::insert_group_data(QString Data)
/*
 *分组表插入新建组数据
 */
{
    if(!insert_check("m_group", "group_name" , Data)){
        QSqlQuery query(account_data_db);
        bool ok = query.exec(QString ("insert into m_group(group_name) values('%1')").arg(Data));
        if(!ok){
//           add_log("account_db::insert_group_data", QStringLiteral("插入组数据失败！"));
           return I_FALSE;
        }
        else{
//           add_log("account_db::insert_group_data", QStringLiteral("插入组数据成功！"));
           return I_SUCCESS;
        }
    }
    else{
//       add_log("account_db::insert_group_data", QStringLiteral("插入组数据失败！已经存在相同的组"));
       return I_EXIST;
    }
}

char account_db::delate_group_data(QString Data)
/*
 *分组表删除新建组数据
 */
{
    if(insert_check("m_group", "group_name" , Data)){
        QSqlQuery query(account_data_db);
        bool ok = query.exec(QString ("DELETE FROM m_group where group_name = '%1'").arg(Data));
        if(!ok)
        {
//           add_log("account_db::insert_group_data", QStringLiteral("删除组数据失败！"));
           return I_FALSE;
        }else
        {
//           add_log("account_db::insert_group_data", QStringLiteral("删除组数据成功！"));
           return I_SUCCESS;
        }
    }
    else{
//       add_log("account_db::insert_group_data", QStringLiteral("删除组数据失败！没有查找到同名组"));
       return I_NOT_EXIST;
    }
}

void account_db::insert_person_data(QString Data)
/*
 *分组表插入新建组数据
 */
{
    if(!insert_check("person", "name" ,Data)){
        QSqlQuery query(account_data_db);
        bool ok = query.exec(QString("insert into person(name) values ('%1')").arg(Data));
        if(!ok)
            ;
//           add_log("account_db::insert_person_data", QStringLiteral("插入人员数据失败！"));
        else
            ;
//           add_log("account_db::insert_group_data", QStringLiteral("插入人员数据成功！"));
    }
    else
        ;
//        add_log("account_db::insert_group_data", QStringLiteral("插入人员数据失败！，存在相同的人名"));
}

char account_db::insert_relation_data(
                                   QString person,
                                   QString amount,
                                   QString group,
                                   QString date,
                                   QString remark)
/*
 *插入关系数据精确的数据
 */
{
//    if( !insert_check(db,"relation", "personName", person)
//            && !insert_check(db,"relation", "time", date)
//            && !insert_check(db,"relation", "amount", amount))
//    {
        QSqlQuery query(account_data_db);
        QString order = QString("insert into relation(personName,amount,time,groupName,remark)"
                                 "values('%1','%2','%3','%4','%5')").arg(person).arg(amount).arg(date).arg(group).arg(remark);
        bool ok = query.exec(order);
        if(!ok){
//           add_log("account_db::insert_relation_data", QStringLiteral("插入关系数据失败！"));
           return I_FALSE;
           }
        else
            return I_SUCCESS;
//    }
//    else{
//        add_log("account_db::insert_relation_data", QStringLiteral("存在相同的关系数据！"));
//        return I_EXIST;
//    }
}

char account_db::delete_relation_data_accurate(
                                   QString person,
                                   QString amount,
                                   QString group,
                                   QString date,
                                   QString remark)
/*
 *删除关系数据精确的数据
 */
{
    if( insert_check("relation", "personName", person)
            && insert_check("relation", "time", date)
            && insert_check("relation", "amount", amount)
            && insert_check("relation", "groupName", group)
            && insert_check("relation", "remark", remark))
    {
        QSqlQuery query(account_data_db);
        QString order = QString("delete from relation where personName = '%1' and amount = '%2' "
                                 "and time = '%3' and groupName = '%4'").arg(person).arg(amount).arg(date).arg(group);
        bool ok = query.exec(order);
        if(!ok){
//           add_log("delete_relation_data_accurate", QStringLiteral("删除关系数据失败！"));
           return I_FALSE;
           }
        else
            return I_SUCCESS;
    }
    else{
//        add_log("delete_relation_data_accurate", QStringLiteral("不存在数据！"));
        return I_NOT_EXIST;
    }
}

void account_db::delete_relation_data_match(QString row_name, QString match_str)
/*
 *删除关系数据中某一个匹配的内容
 */
{
    if(insert_check("relation", row_name, match_str))
    {
        QSqlQuery query(account_data_db);
        QString order = QString("delete from relation where %1 = '%2'").arg(row_name).arg(match_str);
        query.exec(order);
    }
}

/*
**************************************以下部分为 查找数据 的业务处理部分**************
*/
float account_db::count_table_data(QString table)
/*
 * 统计表中所有记录的数据
 */
{
    QSqlQuery query(account_data_db);
    float number = 0;
    query.exec(QString("select * from %1").arg(table));
    while(query.next()){
          number += 1;
    }
    return number;
}

QList<float> account_db::count_amount_all()
/*
 * 统计表中所有 送出/收入 的金额
 * 返回一个统计的列表【收入，送出】
 */
{
    QList<float> total_list;
    QSqlQuery query(account_data_db);
    float positive_num = 0,negative_num = 0;
    query.exec(QString("select amount from relation"));
    while(query.next()){
          float temp = query.value(0).toFloat();
          if(temp >= 0)
              positive_num += temp;
          else
              negative_num += temp;
    }
    total_list.append(positive_num);
    total_list.append(negative_num);
    return total_list;
}

QList<float> account_db::count_amount_group(QString group_name)
/*
 * 统计表中某个组的所有 送出/收入 的金额
 * 返回一个统计的列表【收入，送出】
 */
{
    QList<float> total_list;
    QSqlQuery query(account_data_db);
    float positive_num = 0,negative_num = 0;
    query.exec(QString("select amount from relation where groupName = '%1'").arg(group_name));
    while(query.next()){
          float temp = query.value(0).toFloat();
          if(temp >= 0)
              positive_num += temp;
          else
              negative_num += temp;
    }
    total_list.append(positive_num);
    total_list.append(negative_num);
    return total_list;
}

QList<QString> account_db::select_relation_items( QList<QString> row_name, QList<QString> condition)
/*
 *传入的是两个列表，对应着需要查找的条件。
 *
 */
{
    QList<QString> temp_list;
    QSqlQuery query(account_data_db);
    QString order = "select * from relation where ";
    QString all_condition;
    int size = row_name.size();
    all_condition = row_name[0] + "=" + QString("'%1'").arg(condition[0]);
    order  = order + all_condition;
    for(int i=1;i<size;i++){
        all_condition = row_name[i] + "=" + QString("'%1'").arg(condition[i]);
        order  = order + "and " + all_condition;
    }
    query.exec(order);
    int count =0;
    while(query.next()){
          count += 1;
          QString temp_name = query.value(1).toString();
          QString temp_time= query.value(2).toString();
          QString temp_amount = query.value(3).toString();
          QString temp_group = query.value(4).toString();
          QString temp_remark = query.value(5).toString();
          QString out = QString::number(count)+":"+QStringLiteral(" 姓名:")+temp_name+
                   QStringLiteral(" 时间:")+temp_time+QStringLiteral(" 金额:")+temp_amount+
                   QStringLiteral(" 账簿:")+temp_group+QStringLiteral(" 备注:")+temp_remark;
          temp_list<<out;
    }
    return temp_list;
}

float account_db::count_amount_date(QString date_begin, QString date_end)
/*
 * 统计表中某个组的所有 送出/收入 的金额
 * 返回一个统计的列表【收入，送出】
 */
{

    QSqlQuery query(account_data_db);
    float total = 0;
    QString order = QString("select amount from relation where time >= '%1' and time <= '%2' ").arg(date_begin).arg(date_end);
    query.exec(order);
    while(query.next()){
          float temp = query.value(0).toFloat();
          total += temp;
    }
    return total;
}
